package com.jxgc.excel.demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;

public class ExcelDemo {
    public static void main(String[] args) {
        //读取数据      D:\text
        String path = "D:\\text\\demo(1).xlsx";
        FileInputStream fileInputStream = readExcelStream(path);
        excel( fileInputStream);
    }

    public static void excel (FileInputStream fileInputStream) {
        try {
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            int sheetNumber = workbook.getNumberOfSheets();
            for (int i = 0; i < sheetNumber; i++) {
                Sheet sheet = workbook.getSheetAt(i);  //页
                int firstNumber = sheet.getFirstRowNum();
                int lastNumber = sheet.getLastRowNum();

                System.out.println("--------------------------------------------------------------------------------");

                for (int RowNumber = firstNumber; RowNumber <= lastNumber; RowNumber++) {
                    Row row = sheet.getRow(RowNumber);
                    Cell cell0 = row.getCell(0);
                    System.out.print("姓名：" + getCellValue(cell0) + " ");

                    if("王五11".equals(getCellValue(cell0) )){
                        System.out.printf("");
                    }

                   Cell cell1 = row.getCell(1);
                    System.out.print("年龄：" + getCellValue(cell1) + " ");

                    Cell cell2 = row.getCell(2);
                    System.out.print("职位：" + getCellValue(cell2) + " ");

                    Cell cell3 = row.getCell(3);

                    if(cell3!=null){

                    System.out.println("手机号码：" + getCellValue(cell3) + " ");
                    }else{
                    System.out.println( getCellValue(cell0)+" 手机号码为空");
                    }

                    Cell cell4 = row.getCell(4);
                    System.out.print("学号：" + getCellValue(cell4) + " ");

                    System.out.println();
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
        /*
         * 获取单元格的值
         */
        public static String getCellValue (Cell cell) {
            String value = "";
            DataFormatter dataFormatter = new DataFormatter();
            if (CellType.STRING.equals(cell.getCellType())) {
                value = cell.getStringCellValue();
            } else if (CellType.NUMERIC.equals(cell.getCellType())) {
                value = String.valueOf(cell.getNumericCellValue());
            } else if (CellType.BLANK.equals(cell.getNumericCellValue())){
                value = String.valueOf(cell.getNumericCellValue());
            }else if (CellType.FORMULA.equals(cell.getNumericCellValue())){
                value = String.valueOf((cell.getNumericCellValue()));
            }else {
                value = dataFormatter.formatCellValue(cell);
            }
            return value;
        }


        /*
         * 读取excel文件流
         */
        private static FileInputStream readExcelStream (String path) {
            FileInputStream fileInputStream = null;
            try {
                File file = new File(path);
                if (file.exists()) {
                    fileInputStream = new FileInputStream(file);
                } else {
                    System.out.println("文件不存在");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return fileInputStream;
        }
    }

